"""
数据库会话服务
基于数据库的会话管理和持久化实现
"""

from typing import Dict, Any, Optional, List
from datetime import datetime, timedelta
import uuid
from sqlalchemy.orm import Session as DBSession
from sqlalchemy.exc import SQLAlchemyError, IntegrityError, DatabaseError as SQLDatabaseError
from sqlalchemy import or_, func
from src.utils.logging import get_logger
from src.utils.decorators import handle_errors, timing
from src.exceptions import SessionNotFoundError, DatabaseError
from src.models.session import (
    Session as SessionModel, 
    SessionInteraction as SessionInteractionModel,
    SessionStatus,
    SessionCategory,
    Tag,
    SessionResponse,
    SessionInteractionResponse,
    SessionStats,
    TagResponse
)

logger = get_logger(__name__)

class DatabaseSessionService:
    """基于数据库的会话服务"""
    
    def __init__(self, db_session: DBSession):
        self.db = db_session
    
    def _session_to_response(self, session: SessionModel) -> SessionResponse:
        """
        将Session模型转换为SessionResponse
        
        Args:
            session: Session模型实例
            
        Returns:
            SessionResponse实例
        """
        # 加载标签信息
        tags = [TagResponse.from_orm(tag) for tag in session.tags]
        
        session_dict = {
            "id": session.id,
            "name": session.name,
            "user_id": session.user_id,
            "status": session.status,
            "category": session.category,
            "interaction_count": session.interaction_count,
            "research_count": session.research_count,
            "metadata": session.session_metadata if isinstance(session.session_metadata, dict) else {},
            "created_at": session.created_at,
            "updated_at": session.updated_at,
            "closed_at": session.closed_at,
            "tags": tags
        }
        return SessionResponse(**session_dict)
    
    @handle_errors(default_return=None, log_level="ERROR")
    @timing(log_level="DEBUG")
    def create_session(self, name: Optional[str] = None, user_id: Optional[uuid.UUID] = None, 
                      metadata: Optional[Dict] = None, category: Optional[SessionCategory] = None,
                      tags: Optional[List[str]] = None) -> SessionResponse:
        """
        创建新会话
        
        Args:
            name: 会话名称
            user_id: 用户ID
            metadata: 会话元数据
            category: 会话分类
            tags: 会话标签列表
            
        Returns:
            创建的会话响应
        """
        try:
            # 确保category有默认值
            session_category = category or SessionCategory.DEFAULT
            
            session = SessionModel(
                name=name or "默认会话",
                user_id=user_id,
                session_metadata=metadata or {},
                status=SessionStatus.ACTIVE,
                category=session_category
            )
            
            # 添加标签
            if tags:
                for tag_name in tags:
                    # 检查标签是否已存在，如果存在则使用现有标签
                    tag = self.db.query(Tag).filter(Tag.name == tag_name).first()
                    if not tag:
                        tag = Tag(name=tag_name)
                        self.db.add(tag)
                    session.tags.append(tag)
            
            self.db.add(session)
            self.db.commit()
            self.db.refresh(session)
            
            logger.info(
                f"创建数据库会话: {session.id}",
                extra={"session_name": session.name, "user_id": user_id}
            )
            
            return self._session_to_response(session)
            
        except IntegrityError as e:
            self.db.rollback()
            logger.error(f"创建会话违反数据完整性: {e}", exc_info=True)
            raise DatabaseError(f"创建会话失败，数据完整性错误: {str(e)}", operation="create_session")
        except SQLDatabaseError as e:
            self.db.rollback()
            logger.error(f"创建会话数据库错误: {e}", exc_info=True)
            # 特殊处理表结构不匹配的问题
            if "no column named category" in str(e):
                logger.error("检测到sessions表缺少category列，建议运行数据库修复脚本")
            raise DatabaseError(f"创建会话失败，数据库错误: {str(e)}", operation="create_session")
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"创建会话SQLAlchemy错误: {e}", exc_info=True)
            raise DatabaseError(f"创建会话失败，SQL错误: {str(e)}", operation="create_session")
        except Exception as e:
            self.db.rollback()
            logger.error(f"创建会话未知错误: {e}", exc_info=True)
            raise DatabaseError(f"创建会话失败: {str(e)}", operation="create_session")
    
    @handle_errors(default_return=None, log_level="ERROR")
    def get_session(self, session_id: uuid.UUID) -> SessionResponse:
        """
        获取会话信息
        
        Args:
            session_id: 会话ID
            
        Returns:
            会话响应数据
        """
        session = self.db.query(SessionModel).filter(SessionModel.id == session_id).first()
        if not session:
            raise SessionNotFoundError(str(session_id))
        
        return self._session_to_response(session)

    @handle_errors(default_return=None, log_level="ERROR")
    def update_session(self, session_id: uuid.UUID, updates: Dict) -> SessionResponse:
        """
        更新会话信息
        
        Args:
            session_id: 会话ID
            updates: 更新字段
            
        Returns:
            更新后的会话响应
        """
        try:
            session = self.db.query(SessionModel).filter(SessionModel.id == session_id).first()
            if not session:
                raise SessionNotFoundError(str(session_id))
            
            # 处理标签的特殊逻辑
            if 'tags' in updates:
                tags_value = updates.pop('tags')
                if tags_value is not None:
                    # 清空现有标签
                    session.tags = []
                    # 添加新标签
                    for tag_name in tags_value:
                        tag = self.db.query(Tag).filter(Tag.name == tag_name).first()
                        if not tag:
                            tag = Tag(name=tag_name)
                            self.db.add(tag)
                        session.tags.append(tag)
            
            # 更新其他字段
            for key, value in updates.items():
                if hasattr(session, key):
                    setattr(session, key, value)
            
            # 不需要手动更新updated_at，因为模型中已设置onupdate=datetime.utcnow
            self.db.commit()
            self.db.refresh(session)
            
            return self._session_to_response(session)
            
        except SessionNotFoundError:
            # 重新抛出特定的会话未找到错误
            raise
        except IntegrityError as e:
            self.db.rollback()
            logger.error(f"更新会话违反数据完整性: {e}", exc_info=True)
            raise DatabaseError(f"更新会话失败，数据完整性错误: {str(e)}", operation="update_session")
        except SQLDatabaseError as e:
            self.db.rollback()
            logger.error(f"更新会话数据库错误: {e}", exc_info=True)
            raise DatabaseError(f"更新会话失败，数据库错误: {str(e)}", operation="update_session")
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"更新会话SQLAlchemy错误: {e}", exc_info=True)
            raise DatabaseError(f"更新会话失败，SQL错误: {str(e)}", operation="update_session")
        except Exception as e:
            self.db.rollback()
            logger.error(f"更新会话未知错误: {e}", exc_info=True)
            raise DatabaseError(f"更新会话失败: {str(e)}", operation="update_session")
    
    @handle_errors(default_return=None, log_level="ERROR")
    def add_interaction(self, session_id: uuid.UUID, query: str, response: str, 
                       success: bool = True, research_complete: bool = True,
                       metadata: Optional[Dict] = None) -> SessionInteractionResponse:
        """
        添加会话交互记录
        
        Args:
            session_id: 会话ID
            query: 用户查询
            response: 系统响应
            success: 是否成功
            research_complete: 研究是否完成
            metadata: 交互元数据
            
        Returns:
            交互记录响应
        """
        try:
            # 确保session_id是UUID对象而不是字符串
            if isinstance(session_id, str):
                session_id = uuid.UUID(session_id)
            
            # 首先检查会话是否存在
            session = self.db.query(SessionModel).filter(SessionModel.id == session_id).first()
            if not session:
                raise SessionNotFoundError(str(session_id))
            
            # 创建交互记录
            interaction = SessionInteractionModel(
                session_id=session_id,
                question=query,
                response=response,
                success=success,
                research_complete=research_complete,
                metadata=metadata or {}
            )
            
            # 更新会话统计信息
            current_interaction_count = getattr(session, 'interaction_count', 0)
            setattr(session, 'interaction_count', current_interaction_count + 1)
            
            if research_complete:
                current_research_count = getattr(session, 'research_count', 0)
                setattr(session, 'research_count', current_research_count + 1)
            # 不需要手动更新updated_at，因为模型中已设置onupdate=datetime.utcnow
            
            self.db.add(interaction)
            self.db.commit()
            self.db.refresh(interaction)
            
            logger.info(
                f"添加交互记录到会话 {session_id}",
                extra={
                    "interaction_id": interaction.id,
                    "question": query[:50] + "..." if len(query) > 50 else query
                }
            )
            
            return SessionInteractionResponse.from_orm(interaction)
            
        except SessionNotFoundError:
            # 重新抛出特定的会话未找到错误
            raise
        except IntegrityError as e:
            self.db.rollback()
            logger.error(f"添加交互记录违反数据完整性: {e}", exc_info=True)
            raise DatabaseError(f"添加交互记录失败，数据完整性错误: {str(e)}", operation="add_interaction")
        except SQLDatabaseError as e:
            self.db.rollback()
            logger.error(f"添加交互记录数据库错误: {e}", exc_info=True)
            raise DatabaseError(f"添加交互记录失败，数据库错误: {str(e)}", operation="add_interaction")
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"添加交互记录SQLAlchemy错误: {e}", exc_info=True)
            raise DatabaseError(f"添加交互记录失败，SQL错误: {str(e)}", operation="add_interaction")
        except Exception as e:
            self.db.rollback()
            logger.error(f"添加交互记录未知错误: {e}", exc_info=True)
            raise DatabaseError(f"添加交互记录失败: {str(e)}", operation="add_interaction")
    
    @handle_errors(default_return=[], log_level="ERROR")
    def get_session_history(self, session_id: uuid.UUID, limit: Optional[int] = None) -> List[SessionInteractionResponse]:
        """
        获取会话历史
        
        Args:
            session_id: 会话ID
            limit: 限制返回的记录数
            
        Returns:
            交互历史列表
        """
        session = self.db.query(SessionModel).filter(SessionModel.id == session_id).first()
        if not session:
            raise SessionNotFoundError(str(session_id))
        
        query = self.db.query(SessionInteractionModel).filter(
            SessionInteractionModel.session_id == session_id
        ).order_by(SessionInteractionModel.created_at.desc())
        
        if limit:
            query = query.limit(limit)
        
        interactions = query.all()
        return [SessionInteractionResponse.from_orm(interaction) for interaction in interactions]
    
    @handle_errors(default_return=None, log_level="ERROR")
    def close_session(self, session_id: uuid.UUID) -> SessionResponse:
        """
        关闭会话
        
        Args:
            session_id: 会话ID
            
        Returns:
            关闭后的会话响应
        """
        try:
            session = self.db.query(SessionModel).filter(SessionModel.id == session_id).first()
            if not session:
                raise SessionNotFoundError(str(session_id))
            
            setattr(session, 'status', SessionStatus.CLOSED)
            setattr(session, 'closed_at', datetime.utcnow())
            # 不需要手动更新updated_at，因为模型中已设置onupdate=datetime.utcnow
            
            self.db.commit()
            self.db.refresh(session)
            
            logger.info(
                f"关闭会话: {session_id}",
                extra={"session_name": session.name, "user_id": session.user_id}
            )
            
            return self._session_to_response(session)
            
        except SessionNotFoundError:
            # 重新抛出特定的会话未找到错误
            raise
        except IntegrityError as e:
            self.db.rollback()
            logger.error(f"关闭会话违反数据完整性: {e}", exc_info=True)
            raise DatabaseError(f"关闭会话失败，数据完整性错误: {str(e)}", operation="close_session")
        except SQLDatabaseError as e:
            self.db.rollback()
            logger.error(f"关闭会话数据库错误: {e}", exc_info=True)
            raise DatabaseError(f"关闭会话失败，数据库错误: {str(e)}", operation="close_session")
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"关闭会话SQLAlchemy错误: {e}", exc_info=True)
            raise DatabaseError(f"关闭会话失败，SQL错误: {str(e)}", operation="close_session")
        except Exception as e:
            self.db.rollback()
            logger.error(f"关闭会话未知错误: {e}", exc_info=True)
            raise DatabaseError(f"关闭会话失败: {str(e)}", operation="close_session")
    
    @handle_errors(default_return=None, log_level="ERROR")
    def get_session_stats(self, session_id: uuid.UUID) -> SessionStats:
        """
        获取会话统计信息
        
        Args:
            session_id: 会话ID
            
        Returns:
            会话统计信息
        """
        session = self.db.query(SessionModel).filter(SessionModel.id == session_id).first()
        if not session:
            raise SessionNotFoundError(str(session_id))
        
        # 获取成功的交互数量
        successful_count = self.db.query(SessionInteractionModel).filter(
            SessionInteractionModel.session_id == session_id,
            SessionInteractionModel.success == True,
            SessionInteractionModel.research_complete == True
        ).count()
        
        total_count = getattr(session, 'interaction_count', 0)
        failed_count = total_count - successful_count
        
        success_rate = f"{(successful_count/total_count)*100:.1f}%" if total_count > 0 else "0%"
        
        created_at = getattr(session, 'created_at')
        updated_at = getattr(session, 'updated_at')
        is_active = getattr(session, 'status') == SessionStatus.ACTIVE
        
        return SessionStats(
            session_id=session_id,
            total_interactions=int(total_count),
            successful_research=int(successful_count),
            failed_research=int(failed_count),
            success_rate=success_rate,
            created_at=created_at,
            last_updated=updated_at,
            active=bool(is_active)
        )
